/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.sql.*;
import java.util.ArrayList;
import Module.*;
/**
 *
 * @author CCD gruppe 2
 */
public class UserController {
    /*
     * Object used to connect to the database
     */
    private Database database = new Database();

    private RoleController rolle = new RoleController();
    /**
     * Method used to register a new user
     * @param user
     * @returns the new user
     */
    public User registerUser(User user){
        Connection conn = null;
        User u = new User();
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(""
                    + "insert into users values(DEFAULT,?,?,?,?,?)",autogen);
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.setString(3, user.getWorkplace());
            statement.setString(4, user.getFullname());
            statement.setString(5, user.getExpertise());
            statement.executeUpdate();
            statement = conn.prepareStatement("select * from users where username=?");
            statement.setString(1, user.getUsername());
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                u = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
            }
            rs.close();
            conn.close();
            return u;

        }catch (Exception e) {
            System.err.println(e);
        }
        return u;
    }
    /**
     * Method used to edit a user
     * @param user
     * @return
     */
    public boolean editUser(User user){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(""
                    + "update users set username=?, password=? where userId=?");
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.setInt(3, user.getId());
            statement.executeUpdate();
            conn.close();
            return true;

        }catch (Exception e) {
            System.err.println(e);
        }
        return false;

    }
    /**
     * Method used to edit a user
     * @param user
     * @return
     */
    public boolean deleteUser(User user){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("delete from users where userId=?");
            statement.setInt(1, user.getId());
            statement.executeUpdate();
            conn.close();
            return true;
        }catch(Exception e){
            System.err.println(e);
        }
        return false;

    }
    /**
     * Method used to return the user to a userId
     * @param id
     * @return
     */
    public User getUser(int id){
        Connection conn = null;
        User u=new User();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select * from users where userId=?");
            statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                u =new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
            }
            
            conn.close();
            return u;

        }catch(Exception e){
            System.err.println(e);
        }
        return u;
    }
    /**
     * method used to get all users
     * @return
     */
    public ArrayList<User> getAllUsers(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select * from users order by username");
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                users.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)));
            }
            rs.close();
            conn.close();
            return users;

        }catch(Exception e){
            System.err.println(e);
        }


        return users;
    }

    /**
     * Method used to get all projects to a user
     * @param u
     * @return
     */
    public ArrayList<Project> getProjectForUser(User u){
        Connection conn = null;
        ArrayList<Project> projects = new ArrayList<Project>();
        try{
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement s = conn.prepareStatement(""
                    + "select distinct project.* from project "+
                      "left join usersProject on (project.projectid=usersproject.projectid)" +
                      "left join users on (usersProject.userid=users.userid)" +
                      "where users.userid=?");
            s.setInt(1, u.getId());
            ResultSet r = s.executeQuery();
            while(r.next()){
                Project p = new Project(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5));
                projects.add(p);
            }
            r.close();
            conn.close();
            return projects;
        }catch(Exception e){
            System.err.println(e);
        }
        return projects;
    }
    /**
     * Method used to get the projectleaders
     * @return
     */
    public ArrayList<User> getProjectLeaders(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from users " +
                                    "left join usersrole on(users.userid=usersrole.userid) "+
                                    "where usersrole.roleid = 1");
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6)));
            }
            conn.close();
            r.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /**
     * Method used to get all users who have the role secretaries
     * @return
     */
    public ArrayList<User> getSecretaries(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from users " +
                                    "left join usersrole on(users.userid=usersrole.userid) "+
                                    "where usersrole.roleid = 2");
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6)));
            }
            conn.close();
            r.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /**
     * Method used to get secretaries to a project
     * @param projectId
     * @return
     */
    public ArrayList<User> getAvalibleSecretariesProject(int projectId){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(
                    "select users.* from users " +
                    "left join usersRole on(users.userid=usersRole.userid)" +
                    "left join usersProject on(users.userId=usersProject.userId)" +
                    "where usersRole.roleid = 2 and usersProject.projectId=?");
            s.setInt(1, projectId);
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6)));
            }
            conn.close();
            r.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /**
     * method used to get fasilitatores
     * @return
     */
    public ArrayList<User> getFasilitator(){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from users " +
                                    "left join usersrole on(users.userid=usersrole.userid) "+
                                    "where usersrole.roleid = 3");
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6)));
            }
            conn.close();
            r.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /**
     * Method used to get all users who have the fasilitator role
     * @param projectId
     * @return
     */
    public ArrayList<User> getAvalibleFasilitatorProject(int projectId){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{

            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(
                    "select users.* from users " +
                    "left join usersRole on(users.userid=usersRole.userid)" +
                    "left join usersProject on(users.userId=usersProject.userId)" +
                    "where usersRole.roleid = 3 and usersProject.projectId=?");
            s.setInt(1, projectId);
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6)));
            }
            conn.close();
            r.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }
    /**
     * method used to register rols to a user
     * @param userId
     * @param roles
     */
    public void registerRoleToUser(int userId, ArrayList<String> roles){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            delteRoleToUser(userId);
            PreparedStatement s = conn.prepareStatement("insert into usersRole values(?,?)");
            s.setInt(1, userId);
            for(int i=0;i<roles.size();i++){
                int tall =rolle.getRoleId(roles.get(i));
                s.setInt(2, tall);
                s.executeUpdate();
            }
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }
    }
    /**
     * method used to delete rols to a user
     * @param userId
     */
    private void delteRoleToUser(int userId){
    Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("delete from usersRole where userId=?");
            s.setInt(1, userId);
            s.executeUpdate();
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }

    }
    /**
     * Method used to check if the password is corret
     * @param username
     * @param password
     * @return
     */

    public boolean isCorrectPassword(String username, String password){
    Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from users where userName=? and password=?");
            s.setString(1, username);
            s.setString(2, password);
            ResultSet r = s.executeQuery();
            while(r.next()){
                conn.close();
                return true;
            }
            conn.close();

        }catch(Exception e){

            System.err.println(e);
        }
        return false;
    }
    /**
     * method used to get all users to a team
     * @param teamId
     * @return
     */
    public ArrayList<User> getUsersToTeam(int teamId){
        Connection conn = null;
        ArrayList<User> users = new ArrayList<User>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select distinct users.* from users "+
                "left join usersProject on(users.userId = usersProject.userId) "+
                "left join team on(team.teamId = usersProject.teamId) "+
                "where team.teamId=?");
            s.setInt(1, teamId);
            ResultSet r = s.executeQuery();
            while(r.next()){
                users.add(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6)));
            }
            conn.close();
            r.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return users;
    }


    /**
     * Method used to check if a username already is used
     * @param username
     * @return
     */
    public boolean isUsernameRegistered(String username){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from users where userName=?");
            s.setString(1, username);
            ResultSet r = s.executeQuery();
            while(r.next()){
                conn.close();
                return true;
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }
    /**
     * method used to get the user form  a username
     * @param username
     * @return
     */
    public User getUserForUsername(String username){
        Connection conn = null;
        User user = new User();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from users where userName=?");
            s.setString(1, username);
            ResultSet r = s.executeQuery();
            while(r.next()){
                user = new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5),r.getString(6));
                conn.close();
                return user;
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return user;
    }
    /**
     * method used to login.
     * @param username
     * @param password
     * @return
     */
    public boolean logInn(String username, String password){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from users where userName=? and passWord=?");
            s.setString(1, username);
            s.setString(2, password);
            ResultSet r = s.executeQuery();
            while(r.next()) {
                conn.close();
                return true;
            }
        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }
    /**
     * method used to add users with the correct roles to a project
     * @param projectId
     * @param teamId
     * @return
     */
    public ArrayList<addUserRoles> getUserRolesProject(int projectId, int teamId){
        Connection conn = null;
        ArrayList<addUserRoles> users = new ArrayList<addUserRoles>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "select users.*, usersproject.roleID from users "+
                        "left join usersProject on(users.userId = usersProject.userId) " +
                        "where usersProject.projectId = ? and usersProject.teamId=?");
            s.setInt(1, projectId);
            s.setInt(2, teamId);
            ResultSet r = s.executeQuery();
            while(r.next()) {
                boolean add=true;
                for(int i=0;i<users.size();i++){
                    if(users.get(i).getUser().getUsername().equals(r.getString(2))){
                        add=false;
                    }
                }
                if(add){
                    users.add(new addUserRoles(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5)
                            ,r.getString(6))));
                }
                if(r.getInt(7)==2)users.get(users.size()-1).setFasilitator(true);
                if(r.getInt(7)==3)users.get(users.size()-1).setSecretary(true);
                if(r.getInt(7)==4)users.get(users.size()-1).setCustomer(true);
            }
            r.close();
            conn.close();
            return users;
        }catch(Exception e){
            System.err.println(e);
        }
        return users;

    }
    /**
     * method used to get the userrols to a session
     * @param sessionId
     * @return
     */
    public ArrayList<addUserRoles> getUserRolesSession(int sessionId){
        Connection conn = null;
        ArrayList<addUserRoles> users = new ArrayList<addUserRoles>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "select users.*, usersSession.roleID from users "+
                        "left join usersSession on(users.userId = usersSession.userId) " +
                        "where usersSession.sessionId = ?");
            s.setInt(1, sessionId);
            ResultSet r = s.executeQuery();
            while(r.next()) {
                boolean add=true;
                for(int i=0;i<users.size();i++){
                    if(users.get(i).getUser().getUsername().equals(r.getString(2))){
                        add=false;
                    }
                }
                if(add){
                    users.add(new addUserRoles(new User(r.getInt(1),r.getString(2),r.getString(3),r.getString(4),r.getString(5)
                            ,r.getString(6))));
                }
                if(r.getInt(7)==2)users.get(users.size()-1).setFasilitator(true);
                if(r.getInt(7)==3)users.get(users.size()-1).setSecretary(true);
                if(r.getInt(7)==4)users.get(users.size()-1).setCustomer(true);
            }
            r.close();
            conn.close();
            return users;
        }catch(Exception e){
            System.err.println(e);
        }
        return users;

    }
    /**
     * method used to edit the user info
     * @param user
     * @return
     */
    public boolean editUserInfo(User user){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(""
                + "update users set fullname=?, expertise=?, workplace=? where userId=?");
            statement.setString(1, user.getFullname());
            statement.setString(2, user.getExpertise());
            statement.setString(3, user.getWorkplace());
            statement.setInt(4, user.getId());
            statement.executeUpdate();
            conn.close();
            return true;
        }catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }
    }

